﻿
CREATE PROCEDURE [dbo].[balcon_GetBalanta2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@SurseFinantare varchar(500),
	@FiltruAnalitic TipCont	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON  dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0  AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@surseFinantare)=0 OR (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters(@SurseFinantare,',') fart)))	
	AND (LEN(@FiltruAnalitic)=0 OR (Cont LIKE '%' + @FiltruAnalitic))
	ORDER BY Cont 
					

END